package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.LogStlOperatorSumDao;
import com.jiepos.mpos.common.entity.App_Log_Stl_Mchnt_SumEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 运营商清算交易汇总业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年10月10日 下午2:06:29
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class LogStlOperatorSumService extends BaseService {

	@Autowired
	private LogStlOperatorSumDao logStlOperatorSumDao;

	/**
	 * 列表页数据查询
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @return
	 */
	@SuppressWarnings({"rawtypes"})
	public Map getTransactionLogList(HttpServletRequest request, Pagination pagination,
			App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start, String stl_dt_end,
			String custom_name) {
		JSONObject common = getAllTransLogSqlParams(logStlMchntSumEntity,stl_dt_start, stl_dt_end, custom_name);
		pagination.setQueryString(common.getString("sql"));
		pagination = this.getInsAuthSql(request, pagination, "t1", null);
		pagination = this.getCustomAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " order by t1.stl_dt,t1.amt desc";
		return logStlOperatorSumDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));

	}

	/**
	 * 获取汇总数据
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @param ins_cd
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map getLogStlMchntInfoTotal(HttpServletRequest request, App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity,
			String stl_dt_start, String stl_dt_end, String custom_no, String custom_name, String custom_phone,
			String ins_cd) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("to_char(sum(t1.amt),'fm9999999990.00') as all_amt, "); // 交易总金额
		sqlSb.append("to_char(sum(t1.midfee),'fm9999999990.00') as all_midfee, "); // 会员手续费
		sqlSb.append("to_char(sum(t1.custom_stl_fee),'fm9999999990.00') as all_custom_stl_fee, "); // 会员结算手续费
		sqlSb.append("to_char(sum(t1.ins_stl_fee),'fm9999999990.00') as all_ins_stl_fee, "); // 机构结算手续费
		sqlSb.append("to_char(sum(t1.costfee),'fm9999999990.00') as all_costfee, "); // 成本手续费
		sqlSb.append("to_char(sum(t1.midfee - t1.costfee),'fm9999999990.00') as all_gross_profit, "); // 毛利
		sqlSb.append(
				"to_char(sum((t1.midfee - t1.costfee) - (t1.midfee - t1.ins_stl_fee)),'fm9999999990.00') as all_net_profit "); // 净利
 
/*
		sqlSb.append(
				"to_char(sum(t1.midfee - ((t1.midfee - t1.ins_stl_fee) - (t1.midfee - t1.custom_stl_fee) +(t1.midfee - t1.custom_stl_fee))-t1.costfee) , 'fm9999999990.00') as all_net_profit "); // 净利
*/
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_MCHNT_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.custom_no = t3.custom_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_SERVICE_PARAM_STL + " t6 ");
		sqlSb.append(
				"on t6.ins_uuid = t2.ins_uuid and t6.service_uuid = t4.service_uuid and t5.service_param_uuid = t6.service_param_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INF_CHANNEL + " t7 ");
		sqlSb.append("on t1.channel_no = t7.channel_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
            Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
            sqlSb.append(" and t1.stl_dt >= ? ");
            list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
        }
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
            Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
            sqlSb.append(" and t1.stl_dt <= ? ");
            list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
        }
		if (!StringUtils.isBlank(logStlMchntSumEntity.getCustom_no())) {
			sqlSb.append(" and t3.custom_no = ? ");
			list.add(logStlMchntSumEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append(" and t3.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getLogin_id())) {
			sqlSb.append(" and t3.login_id = ? ");
			list.add(logStlMchntSumEntity.getLogin_id());
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd = ? ");
			list.add(logStlMchntSumEntity.getIns_cd());
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getChannel_no())) {
			sqlSb.append(" and t7.channel_no = ? ");
			list.add(logStlMchntSumEntity.getChannel_no());
		}
		String sql = sqlSb.toString();
		sql = this.getInsAuthSql(request, sql, "t1", null);
		sql = this.getCustomAuthSql(request, sql, "t1", null);
		log.debug("getTransactionLogList queryString: " + sql);
		return logStlOperatorSumDao.queryForMap(sql, list);

	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveLogStlMchntSum(String fileName, HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start,
			String stl_dt_end, String custom_name) {
		JSONObject common = getAllTransLogSqlParams(logStlMchntSumEntity,stl_dt_start, stl_dt_end, custom_name);
		String sql = this.getInsAuthSql(request, common.getString("sql"), "t1", null);
		sql = this.getCustomAuthSql(request, sql, "t1", null);
		sql = sql + " order by t1.stl_dt,t1.amt desc";
		List<Map> resultList = logStlOperatorSumDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("清算日期");
		headList.add("所属机构");
		headList.add("会员编号");
		headList.add("会员名称");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("交易金额");
		headList.add("交易笔数(笔)");
		headList.add("借记卡成本扣率");
		headList.add("借记卡封顶手续费");
		headList.add("贷记卡成本扣率");
		headList.add("会员手续费");
		headList.add("会员结算手续费");
		headList.add("机构结算手续费");
		headList.add("成本手续费");
		headList.add("毛利");
		headList.add("净利");
		headList.add("交易渠道");
		return ExcelUtil.writeExcel(fileName, "运营商清算交易汇总", headList, resultList);
	}

	/**
	 * 会员清算交易汇总导出
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject getAllTransLogSqlParams(App_Log_Stl_Mchnt_SumEntity logStlMchntSumEntity, String stl_dt_start,
			String stl_dt_end, String custom_name) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("t1.stl_dt as stl_dt1, ");
		/*sqlSb.append("t1.stl_dt, ");*/
		sqlSb.append("TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt, ");
		sqlSb.append("t2.ins_cd||'-'||t2.ins_cn_nm as ins, ");
		sqlSb.append("t3.custom_no as custom_no, ");
		sqlSb.append("CONCAT(RPAD (SUBSTR (t3.login_id, 1, 3),LENGTH (t3.login_id) - 4,'*'),SUBSTR (t3.login_id,LENGTH (t3.login_id) - 3,4))||'-'||t3.name as custom_name, ");
		sqlSb.append("t4.service_no||'-'||t4.service_name as service_name, ");
		sqlSb.append("t5.param_no||'-'||t5.param_name as service_param_name, ");
		sqlSb.append("to_char(t1.amt,'fm9999999990.00') as amt, ");
		sqlSb.append("t1.cnt as cnt, ");
		sqlSb.append("to_char(t6.settle_debit_rate,'fm9999999990.00') as settle_debit_rate, ");
		sqlSb.append("to_char(t6.settle_debit_maxfee,'fm9999999990.00') as settle_debit_maxfee, ");
		sqlSb.append("to_char(t6.settle_credit_rate,'fm9999999990.00') as settle_credit_rate, ");
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, "); // 会员手续费
		sqlSb.append("to_char(t1.custom_stl_fee,'fm9999999990.00') as custom_stl_fee, "); // 会员结算手续费
		sqlSb.append("to_char(t1.ins_stl_fee,'fm9999999990.00') as ins_stl_fee, "); // 机构结算手续费
		sqlSb.append("to_char(t1.costfee,'fm9999999990.00') as costfee, "); // 成本手续费
	 
		
		//毛利 = 会员手续费- 成本手续费
		sqlSb.append("to_char(t1.midfee - t1.costfee, 'fm9999999990.00') as gross_profit, ");
		// 净利
		sqlSb.append("to_char((t1.midfee - t1.costfee) - (t1.midfee - t1.ins_stl_fee),'fm9999999990.00') as net_profit, ");
		// 净利
/*
		sqlSb.append(
				"to_char(t1.midfee - ((t1.midfee - t1.ins_stl_fee) - (t1.midfee - t1.custom_stl_fee) +(t1.midfee - t1.custom_stl_fee))-t1.costfee , 'fm9999999990.00') as net_profit, ");
*/
		sqlSb.append("t7.channel_no||'-'||t7.chn_nm as channel_no ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_MCHNT_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.custom_no = t3.custom_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_SERVICE_PARAM_STL + " t6 ");
		sqlSb.append("on t6.ins_uuid = t2.ins_uuid and t6.service_uuid = t4.service_uuid and t5.service_param_uuid = t6.service_param_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INF_CHANNEL + " t7 ");
		sqlSb.append("on t1.channel_no = t7.channel_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getCustom_no())) {
			sqlSb.append(" and t3.custom_no = ? ");
			list.add(logStlMchntSumEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append(" and t3.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getLogin_id())) {
			sqlSb.append(" and t3.login_id = ? ");
			list.add(logStlMchntSumEntity.getLogin_id());
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd = ? ");
			list.add(logStlMchntSumEntity.getIns_cd());
		}
		if (!StringUtils.isBlank(logStlMchntSumEntity.getChannel_no())) {
			sqlSb.append(" and t7.channel_no = ? ");
			list.add(logStlMchntSumEntity.getChannel_no());
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}
	
	
	/**
	 * 获取机构权限拼装sql
	 * 
	 * @param request
	 * @param sq1
	 * @param prefix
	 * @return
	 */
	public String getInsAuthSql(HttpServletRequest request, String sql, String prefix, Map map) {
		if (!StringUtils.isBlank(prefix)) {
			prefix = prefix + ".";
		} else {
			prefix = "";
		}
		String and = "and";
		if (map != null) {
			and = StringUtils.objToString(String.valueOf(map.get("and")));
		}
		StringBuilder sqlBuilder = new StringBuilder();
		// 获取登陆用户名
		String loginName = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_USER));

		String ins_uuid = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));

		String ins_phone = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_PHONE));
		// 如果是机构信息，则查询机构表
		// 如果此用户是属于某个机构，则获取当前机构及下级机构代码
		sqlBuilder.append(" " + and + " " + prefix + "ins_cd in ");
		sqlBuilder.append(" (select tt.ins_cd from " + Table.T_APP_INS_INF + " tt ");
		sqlBuilder.append(" start with tt.ins_uuid='" + ins_uuid + "'");
		sqlBuilder.append(" connect by prior tt.ins_uuid = tt.sup_ins_uuid)");
		sql = sql + sqlBuilder.toString();
		return sql;
	}

	/**
	 * 获取会员权限拼装sql
	 * 
	 * @param request
	 * @param sq1
	 * @param prefix
	 * @return
	 */
	public String getCustomAuthSql(HttpServletRequest request, String sql, String prefix, Map map) {
		if (!StringUtils.isBlank(prefix)) {
			prefix = prefix + ".";
		} else {
			prefix = "";
		}
		String and = "and";
		if (map != null) {
			and = StringUtils.objToString(String.valueOf(map.get("and")));
		}
		StringBuilder sqlBuilder = new StringBuilder();
		// 获取登陆用户名

		String ins_phone = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_PHONE));
		// 如果是机构信息，则查询机构表
		// 如果此用户是属于某个机构，则获取当前机构及下级机构代码
		sqlBuilder.append(" " + and + " " + prefix + "custom_no in ");
		sqlBuilder.append(" (select tt.custom_no from " + Table.T_APP_CUSTOM_INF + " tt ");
		sqlBuilder.append(" start with tt.login_id='" + ins_phone + "'");
		sqlBuilder.append(" connect by prior tt.custom_uuid = tt.sup_custom_uuid)");
		sql = sql + sqlBuilder.toString();
		return sql;
	}
}
